Copyright: OsloData Help Copyright ® 1995 Mike Oslon


CONTENTS
OsloData Help

OsloData is a 16-bit WIndows front-end for databases.

Overview

Procedures

Reporting Bugs

Registering OsloData

Copyright © 1995 Mike Oslon

All questions and comments to moslon@interlog.com


Overview

OsloData is Visual Basic written front-end for databases.

Features

•	Opening multiple databases. Supported formats include: MS Access, dBASE, FoxPro, Btrieve, Paradox, ODBC

•	Creating tables and manipulating their structure

•	Viewing and editing data (data grid and form modes)

•	Creating SQL queries (Select, Crosstab, Update, Delete, Insert and Make-Table queries) and saving them to files

•	Full Access SQL as well as ODBC SQL support

•	Creating and using built-in Access queries (parameters supported)

•	Attaching tables to Access databases

•	Viewing end editing OLE 2.0 objects and pictures (bitmaps: BMP, DIB, RLE, Windows metafiles, and icons) stored in binary fields of tables

•	Importing and exporting data from text files: CSV, TAB, fixed-length, etc.

•	Transferring data between databases

•	Printing data

•	ODBC and IISAM configuration


Procedures

Databases

Creating a Database

Opening a Database

Compacting a Database

Repairing a Database

Logging Into a Database

Browsing Objects

Structure Manipulation

Designing a Table

Data Manipulation

Opening a Table/Query

Working in a Multiuser Environment

Data View Modes

Binary View Modes

Importing a Table

Importing Text

Exporting Data to Text Files

Exporting Data to Databases

Batch Data Export

Query Design

Designing a Query

ODBC and Access SQL

Configuration

Data Object Options

SQL Options

IISAM Configuraion

ODBC Configuration

MS Access Engine

System Files

System Tables


Creating a Database

Menu: File|New MS Access Database...

To create a new MS Access database

•	Choose the name of the file to be created

•	Specify the sorting order for the database's data (Collating Sequence) and choose the version and whether to encrypt the database.

Note: Checking 'Version 1.0' will create a 1.0 Access database only when using MS Access 1.1 Jet engine - MSAJT110.DLL. When using MSAJT112.DLL (Access ver.2.X) a 1.1 Access database will be created.

To create a database other than MS Access, open an empty one in the desired directory.

See also:

Opening a Database


Opening a Database

Menu: File|Open Database...

You can either open an IISAM database or connect to an ODBC database.

Supported IISAM formats include:

  -MS Access 1.X (or 2.X with MS Access 2.X JET engine)
  -dBASE III and IV
  -FoxPro 2.0 and 2.5
  -Btrieve 5.1X
  -Paradox 3.X (or 4.X with MS Access 2.X JET engine)

see also:

MS Access JET Engine

To open a database:

•	Use index tabs to move between IISAM and ODBC dialogs

•	Select database name and type

•	With ODBC, modify the connect string and/or type the user name and password if necessary

•	A new database window will appear listing all tables and queries (in MS Access databases only)


MS ACCESS ENGINE
MS Access JET Engine

Menu: Options|MS Access Engine...

OsloData needs the MS Access JET engine for data access. You can either use Access 1.1 or Access 2.X.
Specify the version in the MS Access Engine dialog box.

Note: Access 2.X engine supports Paradox 4.X, whereas 1.1 only supports 3.X. Type 'Paradox 4.X' in the Database Type box in the Open Database dialog to connect to a Paradox 4.X database.


Compacting a Database

Menu: File|Compact MS Access Database...

MS Access databases tend to increase in size when a lot of data is added and deleted. Compacting frees all the unused space and makes the database smaller in size.

•	Choose the name of the source database to be compacted

•	Choose the name of the target database

•	Specify the sorting order for the database's data (Collating Sequence) and choose the version and whether to encrypt the database.

Note: Checking 'Version 1.0' will create a 1.0 Access database only when using MS Access 1.1 jet engine - MSAJT110.DLL. When using MSAJT112.DLL (Access ver.2.X) a 1.1 Access database will be created.


Repairing a Database

Menu: File|Repair MS Access Database...

When an MS Access database is corrupted (i.e. an updating operation was not completed before an abnormal exit), it can often be repaired.
OsloData attempts to validate all fields and indexes as well as restore the damaged data.


LOGGING INTO A DATABASE
Logging into Password Protected Databases

Specify the default password for logging into a safe Access database. The Login dialog appears when a password is required to open a database.

see also:

System Files


System Files

Menu: Options|System Files...

The System Files dialog box lets you specify:

•	IISAM driver DLLs
DLLs for accessing dBASE, FoxPro, Btrieve and Paradox databases.
Different versions of the MS Access JET engine require different files.

•	MS Access system database
Database containing user accounts for using password-protected MS Access databases.

•	INI file with database settings
An .INI file with all the IISAM settings.


Browsing Objects

Menu: File|Browse Objects...

You can define your own Database and Query profiles for quick access.

•	Click 'New' and 'Edit' to define a new profile or modify an existing one

•	In the Database Profile window, click 'Tables' to select database tables for automatic connection

•	Select one or more entries in the list and click 'Open' or 'Design' to access the profiles


Designing a Table

Menu: Database|Design Table

The Table window allows to modify a Table definition

•	Fields
Use Table|Add Field... to define a new field. Once added, fields cannot be removed.

•	Indexes
Use Table|Add Index... to define a new index. Use Table|Delete Index to remove indexes.

•	Duplicate Defintion
Use Table|Duplicate to create a copy (not appended to the database yet) where you can modify field names and types.

•	Building a Table
Use Table|Build... to append the table to the database


OPENING A TABLE
Opening a Table/Query

Menu: Database|Open Table/Query...

To view and/or edit data stored in a Table or MS Access Query, select one or more Table/Query in the Database window and click the Open button.

•	Sorting Dynasets
Use menu Data/Records|Sort... to select the fields determining the sort order of the Dynaset.

•	Filtering Dynasets
Use menu Data/Records|Filter... to define the filter expression for the Dynaset.

•	Duplicating
Use Data/Records|Duplicate to create a copy of the currently active Recordset.

Note: When working with an ODBC table, a unique index must be set up for the Dynaset to be updatable.

See also:

Designing a Table

Data Object Options

Data View Modes


Data Object Options

Menu: Option|Data Object Options...

In the Data Object Options dialog you can specify various modes for opening database recordsets and executing action queries.

Tables

•	Deny Write
In a multiuser or multitasking environment, others cannot make changes to any records in the Table while you have it open. Use for administrative purposes only.

•	Deny Read
In a multiuser or multitasking environment, others cannot read any records in the Table while you have it open. Use for administrative purposes only.

•	Read Only
You cannot make changes to records in the Table. This can increase the speed of some operations.

Dynasets

•	Deny Write
In a multiuser or multitasking environment, others cannot make changes to any records in any table in your Dynaset while you have it open.  Use for administrative purposes only.

•	Read Only
You cannot make changes to records in your Dynaset. This can increase speed of some operations.

•	Append Only
You can only add new records to the Dynaset. You cannot read or update existing records.

•	Inconsistent Updates
Updates can apply to all fields of the Dynaset, even if they also affect other rows of the Dynaset.

•	Consistent Updates
(Default) Updates apply only to those fields that will not affect other rows of the Dynaset.

•	SQL PassThrough
For Dynasets created with an SQL statement, this value causes the SQL statement to be passed to an ODBC database for processing. The Dynaset is not updatable.

Action Queries

•	Deny Write
In a multiuser environment, others cannot make changes to records in any table in the target recordset while the statement is running.

•	Inconsistent Updates
(Default) Updates can apply to all fields of the target recordset, even if they also affect other rows of the target recordset.

•	Consistent Updates
Updates apply only to those fields that will not affect other rows of the target recordset.

•	SQL PassThrough
This value causes the SQL statement to be passed to an ODBC database for processing.

•	Generate SQL for Dynasets
When opening a table as a Dynaset, an SQL string is generated bypassing some table verifications.

•	Always Create Dynaset
When opening an existing table, a Dynaset object is always created as opposed to a Table object. Dynasets can be filtered and sorted, Tables can be searched with an index.


Data View Modes

Menu: View|Data Grid/Data Form

•	Data Grid
Allows to view text data in the read-only mode in a table.

•	Data Form
Allows to move between individual records of a Recordset. You can modify data, update and delete records, view OLE objects and pictures stored in long binary fields.


Working in a Multiuser Environment

When working with a Table or a Dynaset, use the menu Data/Records|Table/Dynaset Properties... to change the locking mode:

•	Pessimistic (default)
The page containing the current record is not available to others while you are updating the field values.

•	Optimistic
The page containing the current record is not available to others only during the actual process of writing data to the database after all the field values are changed.

In a multiuser or multitasking environment, where there is not enough time for background processing to keep all records current in your recordset, use System|Free Locks to suspend data processing, allowing the database to release locks on record pages and make all data in your local recordset current in a multiuser environment.


Binary View Modes

Menu: <OLE Name>|View Mode... (Picture/Binary|View Mode...)

Select the format to view the data stored in binary fields of a Table:

•	Text
view binary characters as text

•	Picture
display a picture stored in the field
Supported formats include
  -bitmaps (BMP, DIB, RLE)
  -icons (ICO)
  -Windows metafiles (WMF)

•	OLE
display an OLE 2.0 object stored in the field
OLE objects are in the format of the MS Access OLE Object fields.


IMPORTING DATA
Importing a Table

Menu: Database|Import Table...

To import data from a database

•	Select the source database

•	Specify the source table, target table name and field names/types and whether the copy data and/or indexes

•	The 'Use SQL' option provides a greater speed but a lower compatibilty with databases of various types. Deselect it if OsloData fails to import a table.


Importing Text

Menu: Database|Import Text...

To import data from a text file

•	Select the source file

•	Specify the format, rows to scan (optional), target table and field names/types, and whether the first row contains the field names (Column Name Header)

•	For fixed-length files, you need to divide your data into columns. Click Parse to identify the columns

The supported text file formats include:
  -comma delimited
  -tab delimited
  -custom delimited
  -fixed-length


Exporting Data to Text Files

Menu: Data/Records|Export to Text File...

To copy data from the active open recordset to a text file

•	Select the target file

•	Specify the format, rows to scan (optional), target table and field names/types, and whether to store the field names in the first row (Column Name Header)

The supported text file formats include:
  -comma delimited
  -TAB delimited
  -custom delimited
  -fixed-length

see also:

Exporting Data to Databases


Exporting Data to Databases

Menu: Data/Records|Export to Database...

To copy data from the active open recordset to a database

•	Select the target database

•	Specify the number of rows to scan (optional), target table name and field names/types

•	The 'Use SQL' option provides a greater speed but a lower compatibilty with databases of various types. Deselect it if OsloData fails to export a table.

see also:

Exporting Data to Text Files


Batch Data Export

Menu:
Database|Export to Text File...
Database|Export to Database...

To export data from the tables of a database

•	Select one or more tables in the Database window

•	All the selected tables will be exported to the target directory/database

See also:

Exporting Data to Text Files

Exporting Data to Databases


DESIGNING QUERIES
Designing SQL Queries

Menu: Database|New Query...

You can visually create SQL statements in the Query window.

•	Tables
Use Query|Add Tables... to include new tables into the SQL statement and Query|Remove Table to individually remove them.

•	Fields
In each table (list of fields) select or deselect items.
Selected field names appear in the lower part of the window under the Select index tab.
Drag and drop the selected fields to change their order in the resultant recordset. Double-click on field names for details (Alias is the field name in the resultant recordset). To include a computed field, double-click on the empty item in the list box.

•	Sorting
Click on the Sort index tab. Drag and drop the selected fields to the Sort list box to include them in the ORDER BY SQL clause.
Press <Del> to remove the fields from the lists.

•	Table Correlation Names
Use Query|Table Correlation Names... to define the SQL correlation names for the included tables. Tables added more than once are automatically given a name of <table>_<1, 2, 3, etc.>

•	Joins
Drag and drop fields from the source table to the target table. Click on a join for details.

•	Parameters
Use Query|Parameters... to define the parameters and their types.

•	Criteria
Click on the Where index tab. Double-click on the list box to define criteria.

•	Crosstab Queries
In the Field options dialog (double-click on a field name under the Select index tab), select
------------------------------------------------------------------------------
  -Show	for row headings
  -Value	for the summarized data
  -Column Heading      	for column headings of the resultant table
------------------------------------------------------------------------------

Drag the selected fields into the Group list box to include them in the GROUP BY clause.

•	Action Queries
Select Append, Delete, Update, or Make-Table in the Query Options dialog (use Query|Query Options).

•	Generating SQL
Use the menu Query|AutoGenerate to turn the automatic SQL generation mode on or off. In the Query Options dialog, specify ODBC SQL (as opposed to Access SQL) if necessary.

•	Executing Queries
Use Query|Execute to create a resultant recordset or execute an action query.
Note: you can specify SQL PassThrough in the Data Object Options dialog to bypass the MS Access SQL evaluation DLL.

•	Saving Definitions
Use Query|Save to File... to save your query definitions to .QRD files. You can then open them from the Database window by clicking the Design button.

•	Creating MS Access Built-in QueryDefs
For MS Access based queries, use Query|Create... to create a permanent QueryDef object in an Access database.

see also:

SQL Options

Data Object Options

Differences between Access and ODBC SQL

Opening a Query File


SQL Options

Menu: Options|SQL Options...

The SQL Options dialog provides configuration for the SQL generation.

•	No Line Breaks
No line breaks are inserted into the generated SQL string.

•	Remove Owners from Table Names
With some ODBC databases, table names appear with an owner (e.g. DBA.users). It is removed for SQL to work correctly with the tables.

•	Always Use Where Clause
In Access SQL, no INNER JOIN clauses are used. Instead, table joins are described with the WHERE clause.

•	Automatically append to Same Fields
In Append queries, the target (append to) fields are assumed to have the same name as the source fields.

•	Automatically Create Joins
After a new table is added in the Query Window, table joins for fields with identical names and types are created.


Differences between Access and ODBC SQL

The following are the major differences between Access and ODBC SQL syntaxes:

•	ODBC: using WHERE clause rather than INNER JOIN clause

•	ODBC: LEFT/RIGHT OUTER JOIN instead of LEFT/RIGHT JOIN

•	ODBC: using ' ' rather than " " for quoting text

•	ODBC: using " " rather [ ] for bracketing illegal field and table names

•	Table correlation name syntax:
ODBC: <table name> <corr. name>
Access: <table name> AS <corr. name>


Opening a Query File

Menu: Database|Design Query...

•	Select your .QRD file containg a Query definition.

•	A new Query window will appear with the included tables and joins

see also:

Designing SQL Queries


IISAM Configuration

Menu: Options|Configure IISAMs...

In the Configure IISAMs dialog box, you can specify optional setting for accessing various databases.

Setting	Default

ISAM

-MS Access databases

•	PageTimeout	5 (0.5 s)
time in 0.1 s to wait before another 2K page of data is added to a Dynaset

•	MaxBufferSize	512 (512K)
maximum size of memory cache to hold dynaset data before creating temporary files

•	LockRetry       	20
maximum number of retries to place a lock on a table

•	CommitLockRetry	20
maximum number of retries to place a lock on the pages in a transaction before Rollback

•	ReadAheadPages  	8 (8 2K pages)
number of 2K pages beyond the current position of the record pointer paged into a Dynaset

dBASE ISAM

-dBASE and FoxPro databases

•	PageTimeout	60 (600 s)
time in 0.1 s to wait before another 2K page of data is added to Dynasets

•	CollatingSequence  	ASCII
sort order order of text fields

•	Century	OFF
corresponds to dBASE SET CENTURY {ON|OFF}

•	Date	AMERICAN
corresponds to dBASE SET DATE

•	Mark	47
corresponds to dBASE SET MARK (decimal ASCII value of the mark character)

•	Deleted	ON
corresponds to dBASE SET DELETED {ON|OFF} (ON causes the Access engine to disregard deleted records)

Paradox ISAM

-Paradox databases

•	PageTimeout	60 (600 s)
time in 0.1 s to wait before another 2K page of data is added to a Dynaset

•	CollatingSequence 	ASCII
sort order order of text fields

•	ParadoxUserName
identifies user who attempts to place a lock on a Paradox record

•	ParadoxNetPath	C:/
path to PARDOX.NET required in a multiuser environment

btrieve

-Btrieve databases

•	Options	/m:64 /p:4096 /b:16 /f:20 /l:40 /n:12 /t:BTRIEVE.TRN
entry in WIN.INI

Option	Description       	                 	Required Value
------------------------------------------------------------------------------------
   /m	Memory size	<=38
   /p	Page size	4096
   /b	Pre-image buffer size	16
   /f	Open files	<=4
   /l	Multiple locks	>=2 * setting    for /f
   /n	Files in a transaction	>=4
   /t	Transaction filename	path to .TRN file
   /u	Compression buffer size      	>= length in K of the longest record in your data files
   /i	Pre-image file drive	-
   /c	Index compaction	-
   /a	Activate logging     	-
   /s	Discard unneeded segments	-
------------------------------------------------------------------------------------

ODBC

-ODBC databases

•	TraceSQLMode	0
1 = trace SQL statements sent to SQLOUT.TXT in the current directory; 0 = don't trace

•	QueryTimeuot	60
maximum time to complete a query, in seconds

•	LoginTimeout	20
maximum time to log into a database, in seconds

•	ConnectionTimeout	600
idle time before a connection is closed, in seconds

•	AsyncRetryInterval  	500
interval between inqueries to the server whether it's finished, in milliseconds

•	AttachCaseSensitive	0
1 = attach only to table exactly matching the specified name; 0 = ignore case

•	SnapshotOnly	1
1 = create Dynasets when possible, 0 = always create Snapshots

•	AttachableObjects   	TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM
table types listed when a connection established, vary in different RDBMSs

Debug

-tracing ODBC API calls

•	RmtTrace  	0
0 = use asynchronous query execution if possible and don't trace ODBC API calls
8 = use asynchronous query execution if possible and trace ODBC API calls into ODBCAPI.TXT
16 = use synchronous query execution if possible and don't trace ODBC API calls
24 = use synchronous query execution if possible and trace ODBC API calls into ODBCAPI.TXT

OsloData needs to be restarted for the changes to take place.


ODBC Configuration

Menu: Options|Configure ODBC...

Setup ODBC data sources and drivers in the Configure ODBC dialog.
Click Install to add a new ODBC driver using a standard ODBC.INF file.


System Tables

Menu: View|Include System Tables...

In the database window, tables can be listed including the system objects that vary depending on the database type. By default, OsloData does not include system tables.


Reporting Bugs

Please report bugs to:

     Michael Oslon
     35 Canyon Ave. #1604
     Downsview, Ontario
     M3H 4Y2
     Canada

or
    moslon@interlog.com

If you notice a considerable bug, you will recieve a free updated registered copy of OsloData.


Registering OsloData

    Register and receive an updated, fully functioning version of OsloData. To order a copy of OsloData, send me (Michael Oslon) the order form and a check for:

    a single copy: US$30 (or Can$40)
    2 to 10 copies: US$20 (or Can$28) each
    11 to 50 copies: US$15 (or Can$20) each
    51 or more copies: US$9 (or Can$12) each

If you wish to receive OsloData by mail, all prices should include shipping (US $6)

     Michael Oslon
     35 Canyon Ave. #1604
     Downsview, Ontario
     M3H 4Y2
     Canada

Order Form

    After registering, you will be able to upgrage to any newer version during the following three months for the shipping price only (or free of charge by e-mail).


Order Form

Name: ____________________________________________________

Address: _________________________________________________
   _______________________________________________________

Where did you hear about OsloData? _______________________
   _______________________________________________________

Comments: ________________________________________________
   _______________________________________________________
   _______________________________________________________

How would you like to receive OsloData?
[ ] By mail
[ ] By e-mail. E-Mail address: ___________________________


Append

An Append Query adds new records to an existing table.


Crosstab

A Crosstab Query shows data arranged as a table with customizable row and column headings.


CSV

Comma delimited files.


Delete

A Delete Query deletes records from a table according to the criteria.


IISAM

Installable ISAM databases. Include MS Access, xBASE, Btrieve and Paradox. OsloData does not use the ODBC interface to connect to them.


Make-Table

A Make-Table Query creates a table definition and fills it with data.


MS Access JET engine

DLL for accessing MS Access databases as well as other ISAMs. MSAJT110.DLL is for Access 1.1 and lower; MSAJT200.DLL is for Access 2.0 and lower. MSAJT200.DLL is accessed via MSAJT112.DLL..


ODBC

Open Database Connectivity. Standard for accessing databases of various formats via the ODBC Driver Manager (ODBC.DLL).


Page

A portion of the database file containg record data. A page is 2K in size and may contain more than one record.


SQL

Standard language for querying databases. OsloData supports Access and ODBC SQL dialects.


TAB

TAB delimited files.


Update

An Update Query changes field values in an existing table for the matching records.